package cn.com.nes.site.module.dao.crm;

import cn.com.nes.common.util.*;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import cn.com.nes.site.module.dao.CompanyDaoImpl;
import cn.com.nes.site.module.dao.UserDaoImpl;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSONArray;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;

import javax.annotation.Resource;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

@Repository
public class ClueDaoImpl extends BaseDaoImpl {
	private TransactionStatus newg_status = null;

	@Resource
	private UserDaoImpl userDaoImpl;

	@Resource
	private CompanyDaoImpl companyDaoImpl;
	//查询线索列表
	public PageData getClueList(DataReq req) {
		PageData res = new PageData();
		Map<String, Object> param = req.getReqData();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		try {
			String userCompanyId = req.getUinfo().getCompanyId();
			//线索目列表
			sb.delete(0, sb.length()) ;
			sb.append("SELECT a.newshopBookingId,p.name newshopBookingName,a.customeraddress,a.businessdirection,a.uuid,a.clueowner,n.nickName clueownername,a.cluename,a.cluesource,a.cluephone,a.cluecompany,a.companycontact,if(d.xzqhqc is null ,a.numberhome,d.xzqhqc) xzqhmc,a.personincharge,a.personphone, date_format(b.visittime,'%Y-%m-%d %T') latelyClaimTime,b.uuid visituuid ,  date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.modifyTime,'%Y-%m-%d %T') modifyTime,a.modifyUid,if(e.name is null ,a.pooltype,e.name) pooltype,f.name culestate,a.projectname,b.userid,b.username,g.name visittype ,b.visitaddress,date_format(b.visittime,'%Y-%m-%d %T') visittime,b.visitcontent,c.nickName modifyusername ,h.departmentName modifydepartmentName,c.phone modifyuserphone ,k.name modifyusersex,a.sex,a.numberhome,tc.companyName ") ;
			sb.append(" ,a.followupstatus,tp.name followupstatusname,if(a.cluebelongtocompany ='").append(userCompanyId).append("' ,TRUE,FALSE) isFp ");
			sb.append(" from clue a  ") ;
			sb.append(" left JOIN (SELECT m.clueId,max(m.visittime) visittime,max(m.modifyTime) vMaxTime,m.uuid,m.userid ,m.username,m.visitaddress,m.visitcontent,m.visittype ").append("from log_claim m GROUP BY clueId ) b on a.uuid=b.clueId  ") ;
			sb.append(" left JOIN t_user c on a.modifyUid=c.id  ") ;
			sb.append(" left JOIN t_xzqh d on a.xzqhdm=d.xzqhdm  ") ;
			sb.append(" left JOIN t_param tp on a.followupstatus=tp.`value` and tp.param_type='11' ") ;
			sb.append(" left JOIN t_param e on a.pooltype=e.`value` and e.param_type='14'  ") ;
			sb.append(" left JOIN t_param f on a.culestate=f.`value` and f.param_type='15'  ") ;
			sb.append(" left JOIN t_param g on b.visittype=g.`value` and g.param_type='19'  ") ;
			sb.append(" left JOIN t_department h on c.departmentId=h.departmentId ") ;
			sb.append(" left JOIN (SELECT m.clueId,max(m.createTime) vMaxTime ").append("from log_operate m GROUP BY clueId ) i on a.uuid=i.clueId  ");
			sb.append(" left JOIN t_param k on c.sex=k.`value` and k.param_type='28'  ") ;
			sb.append(" left JOIN t_user n on a.clueowner=n.id  ") ;
			sb.append(" left join t_param p on a.newshopBookingId=p.`value` and p.param_type='200' ");
			sb.append(" left join t_company tc on a.cluebelongtocompany=tc.companyId and tc.sfsc=0 ");
			
		    sb.append("  where sfzx='1' and a.isdelete='0'  ");
			if(!"0000".equals(userCompanyId)){
				sb.append(" and a.cluebelongtocompany like concat('"+ userCompanyId +"','%')");
			}
		    //type区分公海线索和我的客户 'mine'我的客户 'ghxs'公海线索
			String type= (String) param.get("type");
			//区分是否为管理员 '04'是管理员
			String userType=req.getUinfo().getUserType();


			if(!Globals.DEMO_COMPANYID.equals(userCompanyId)) {//不是演示账号
				if(!"04".equals(userType)){//不是管理员公海不加线索，我的客户自己看自己的
					// sb.append(" and ((1=1  ");
					// sb.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(),"a.xzqhdm"));
					// sb.append(" ) ");
					// sb.append(" or a.clueowner='").append(req.getUinfo().getId()).append("' ");
					// sb.append(" ) ");
					if(type.equals("mine")){
						sb.append("  and a.clueowner =  '").append(req.getUinfo().getId()).append("' ");
					}
				}
            }

			if(type.equals("mine")){
				// sb.append(" and a.clueowner is not null and a.clueowner <> '' ");
				if (!"0000".equals(userCompanyId)){
					sb.append(" and a.clueowner ='").append(req.getUinfo().getId()).append("' ");
				}
			}


			//线索所有人
			if(!StringUtils.isEmpty((String)param.get("quyclueowner"))) {
				sb.append("  and a.clueowner =  '").append(param.get("quyclueowner")).append("' ");
			}

			//组合查询
			if(!StringUtils.isEmpty((String)param.get("comquery"))) {
				sb.append("  and ( a.cluename like  '%").append(param.get("comquery")).append("%' or a.personincharge like  '%").append(param.get("comquery")).append("%' or a.cluephone like  '%").append(param.get("comquery")).append("%' or a.cluecompany like  '%").append(param.get("comquery")).append("%' or a.projectaddress  like  '%").append(param.get("comquery")).append("%' or a.wechat like  '%").append(param.get("comquery")).append("%' or a.companycontact like '%").append(param.get("comquery")).append("%' ) ");
			}
			
			//企业身份
			if(!StringUtils.isEmpty((String)param.get("pooltype"))) {
				sb.append("  and a.pooltype =  '").append(param.get("pooltype")).append("' ");
			}
			
			//线索阶段
			if(!StringUtils.isEmpty((String)param.get("cluestage"))) {
				sb.append("  and a.cluestage =  '").append(param.get("cluestage")).append("' ");
			}
			
			//线索状态
			if(!StringUtils.isEmpty((String)param.get("culestate"))) {
				sb.append("  and a.culestate =  '").append(param.get("culestate")).append("' ");
			}
			
			//跟进状态
			if(!StringUtils.isEmpty((String)param.get("followupstatus"))) {
				sb.append("  and a.followupstatus =  '").append(param.get("followupstatus")).append("' ");
			}
			
			//行政区划
			if(!StringUtils.isEmpty((String)param.get("xzqhdm")) && !"86".equals((String) param.get("xzqhdm"))) {
				sb.append("  and a.xzqhdm like  '").append(XzqhUtil.getQueryXzqh(this.getJdbcTemplate_newg(),(String)param.get("xzqhdm"))).append("%' ");
			}

			//线索流量来源
			if(!StringUtils.isEmpty((String)param.get("cluesource"))) {
				sb.append("  and a.cluesource =  '").append(param.get("cluesource")).append("' ");
			}
			
			//工作台数据概览跳转查询
			if(!StringUtils.isEmpty((String) param.get("dateType"))){
            	String dateType = String.valueOf(param.get("dateType")) ;
            	String query = dateTypeCondition_mod(dateType);
            	sb.append(query);
			}

			//工作台待办跳转
			if(!StringUtils.isEmpty((String) param.get("wb_db"))) {
				if("01".equals(param.get("wb_db"))) {
					sb.append("and (b.vMaxTime<date_sub(curdate(),INTERVAL 30 day) or i.vMaxTime<date_sub(curdate(),INTERVAL 30 day)) and a.followupstatus!='03' ");
				}
			}
			
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] {});
			sb.append(" order by a.modifyTime desc,a.serial asc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] {});
			res.setPage(list);	
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {

		}
		return res;
	}
	
	public PageData addClue(Connection conn,DataReq req) throws UnsupportedEncodingException, SQLException {
		Map<String, Object> param = req.getReqData();
		//是否向客户表插入数据
		Boolean isAddCustomer = ObjectUtil.isEmpty(param.get("isAddCustomer")) ? true : (Boolean) param.get("isAddCustomer");
		PageData res = new PageData();
		param.put("tableName", "clue");
		param.put("isdelete", "0");
		
		//经纬度问题
		param.put("longitude", null);
		param.put("latitude", null);
		
		//添加操作日志
		param.put("OperationLogType", "01");
		param.put("OperationLogId", "updateClueId");
		
		param.put("cluebelongtocompany", ObjectUtil.isEmpty(param.get("cluebelongtocompany")) ? req.getUinfo().getCompanyId() : param.get("cluebelongtocompany"));
        if(param.get("cluestage")==null || "01".equals(param.get("cluestage"))  || "".equals(param.get("cluestage"))){
        	param.put("followupstatus","01");
        	param.put("cluestage","01");
        }else if((String)param.get("cluestage")!=null && ("02".equals(param.get("cluestage")) || "03".equals(param.get("cluestage")) )) {
        	param.put("followupstatus","02");
        }else if((String)param.get("cluestage")!=null && ("04".equals(param.get("cluestage")) || "05".equals(param.get("cluestage")) )){
        	param.put("followupstatus","03");
        }
        if(StringUtils.isEmpty((String) param.get("projectarea"))) {
        	param.remove("projectarea");
        }
        String clue_uuid ="";
	        //如果企业联系人手机号更换，则将原来联系人手机号所有的线索都换成新手机号-start
	        String cluephone=(String) param.get("cluephone");
	        String oldcluephone=(String) param.get("oldcluephone");
	        if(!cluephone.equals(oldcluephone)){
				Map<String, String> obj = HttpUtils.getPhoneAddress(cluephone);
				String areaBelong = obj.get("city") + obj.get("province");
	        	updatePhone(conn,oldcluephone,cluephone,areaBelong,req.getUinfo().getCompanyId());
	        }
	        //如果企业联系人手机号更换，则将原来联系人手机号所有的线索都换成新手机号-end
		
			//拓展字段用
			String jsondata = (String) param.get("jsondata") ;
			if(!StringUtils.isEmpty(jsondata)) {
				jsondata =  URLDecoder.decode(jsondata, "UTF-8");
				param.put("jsondata", jsondata);
			}
			
			String uuid=(String)param.get("uuid");
			clue_uuid = uuid ;
//			//修改不校验线索手机号是否重复，重复的当成新的线索
//			if("1".equals(param.get("isRl"))){
//				param.put("clueowner",req.getUinfo().getId());
//				param.put("culestate", "01");
//			}else {
//				param.put("culestate", "02");
//			}
			if(StringUtils.isEmpty(uuid)) {
				//新增线索时先将同公司同手机号的其他线索sfzx置为0
				//修改不校验线索手机号是否重复，重复的当成新的线索
				//updateSfzx(conn,cluephone,req.getUinfo().getCompanyId());
				Map<String, String> obj = HttpUtils.getPhoneAddress(cluephone);
				String areaBelong = obj.get("city") + obj.get("province");
				param.put("numberhome", areaBelong);
				if("1".equals(param.get("isRl"))){
					param.put("clueowner",req.getUinfo().getId());
					param.put("culestate", "01");
				}else {
					param.put("culestate", "02");
				}
				if(StringUtils.isEmpty((String) param.get("poolentrymode"))) {
					param.put("poolentrymode","05");
				}
				param.put("OperationLogName", "增加线索");
				res =  insertTableData(conn, req) ;
				clue_uuid = res.getKey();
			}else {
				//认领状态-start
//		        //根据手机号和公司查询该线索之前的线索的认领状态
//				StringBuffer sb = new StringBuffer();
//				sb.append(" select clueowner,culestate from clue where cluephone='").append((String)param.get("cluephone")).append("' and cluebelongtocompany='").append(req.getUinfo().getCompanyId()).append("' order by createTime desc LIMIT 1 ");
//				List<Map<String, Object>> list = this.queryForList(conn,sb.toString(),new Object[] {});
//				String clueowner=null;
//				String culestate="02";
//				if(list.size()>0){
//					clueowner=(String)list.get(0).get("clueowner");
//					culestate=(String)list.get(0).get("culestate");
//				}
//		        if(!StringUtils.isEmpty((String) param.get("isRl"))){
//		        	if("1".equals(param.get("isRl"))){
//		        		if(req.getUinfo().getId().equals(clueowner)){//如果认领人和之前认领人相同
//		        			param.put("clueowner",req.getUinfo().getId());
//		            		param.put("culestate", "01");
//		        		}else{//如果认领人和之前认领人不相同
//		    				res.setRetCode(9);
//		    				res.setRetMessage("此线索已经被认领，请与认领人商议自行转移线索！！！");
//		    				return res;
//		        		}
//		        	}else{
//		        		param.put("clueowner",clueowner);
//		        		param.put("culestate",culestate);
//		        	}
//		        }
//		        //认领状态-end
				param.put("keys", "uuid");
				param.put("OperationLogName", "编辑线索信息");
				res =  updateTableData(conn, req) ;
			}
			if (isAddCustomer)
				//新增公海线索同时加一条客户信息
				saveCustomer(conn, req);
			if(res.getRetCode()==0) {
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey(clue_uuid) ;
			}else{
				res.setRetCode(9);
				res.setRetMessage("error");
			}
		return res;
	}
	
//	public PageData addClue_old(Connection conn,DataReq req) {
//		Map<String, Object> param = req.getReqData();
//		PageData res = new PageData();
//		newg_status = begin_newg();// 开启事物
//		param.put("tableName", "clue");
//		param.put("isdelete", "0");
//		
//		//经纬度问题
//		param.put("longitude", null);
//		param.put("latitude", null);
//		
//		//添加操作日志
//		param.put("OperationLogType", "01");
//		param.put("OperationLogId", "updateClueId");
//		
//		param.put("cluebelongtocompany", req.getUinfo().getCompanyId());		
//        if((String)param.get("cluestage")!=null && "01".equals(param.get("cluestage"))){
//        	param.put("followupstatus","01");
//        }else if((String)param.get("cluestage")!=null && ("02".equals(param.get("cluestage")) || "03".equals(param.get("cluestage")) )) {
//        	param.put("followupstatus","02");
//        }else if((String)param.get("cluestage")!=null && ("04".equals(param.get("cluestage")) || "05".equals(param.get("cluestage")) )){
//        	param.put("followupstatus","03");
//        }
//        if(StringUtils.isEmpty((String) param.get("projectarea"))) {
//        	param.remove("projectarea");
//        }
//        String clue_uuid ="";
//        try {
//	        
//	        //如果企业联系人手机号更换，则将原来联系人手机号所有的线索都换成新手机号-start
//	        String cluephone=(String) param.get("cluephone");
//	        String oldcluephone=(String) param.get("oldcluephone");
//	        if(!cluephone.equals(oldcluephone)){
//	        	updatePhone(oldcluephone,cluephone,req.getUinfo().getCompanyId());
//	        }
//	        //如果企业联系人手机号更换，则将原来联系人手机号所有的线索都换成新手机号-end
//		
//			//拓展字段用
//			String jsondata = (String) param.get("jsondata") ;
//			if(!StringUtils.isEmpty(jsondata)) {
//				jsondata =  URLDecoder.decode(jsondata, "UTF-8");
//				param.put("jsondata", jsondata);
//			}
//			
//			String uuid=(String)param.get("uuid");
//			clue_uuid = uuid ;
//			if(StringUtils.isEmpty(uuid)) {
//				//新增线索时先将同公司同手机号的其他线索sfzx置为0
//				updateSfzx(cluephone,req.getUinfo().getCompanyId());
//				
//				if(StringUtils.isEmpty((String) param.get("poolentrymode"))) {
//					param.put("poolentrymode","05");
//				}
//				param.put("OperationLogName", "增加线索");
//				res =  insertTableData(newg_status, req) ;
//				clue_uuid = res.getKey();
//			}else {
//				//认领状态-start
//		        //根据手机号和公司查询该线索之前的线索的认领状态
//				StringBuffer sb = new StringBuffer();
//				sb.append(" select clueowner,culestate from clue where cluephone='").append((String)param.get("cluephone")).append("' and cluebelongtocompany='").append(req.getUinfo().getCompanyId()).append("' order by createTime desc LIMIT 1 ");
//				List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString());
//				String clueowner=null;
//				String culestate="02";
//				if(list.size()>0){
//					clueowner=(String)list.get(0).get("clueowner");
//					culestate=(String)list.get(0).get("culestate");
//				}
//		        if(!StringUtils.isEmpty((String) param.get("isRl"))){
//		        	if("1".equals(param.get("isRl"))){
//		        		if(req.getUinfo().getId().equals(clueowner)){//如果认领人和之前认领人相同
//		        			param.put("clueowner",req.getUinfo().getId());
//		            		param.put("culestate", "01");
//		        		}else{//如果认领人和之前认领人不相同
//		        			commit_newg(newg_status);
//		    				res.setRetCode(9);
//		    				res.setRetMessage("此线索已经被认领，请与认领人商议自行转移线索！！！");
//		    				return res;
//		        		}
//		        	}else{
//		        		param.put("clueowner",clueowner);
//		        		param.put("culestate",culestate);
//		        	}
//		        }
//		        //认领状态-end
//				
//				param.put("keys", "uuid");
//				param.put("OperationLogName", "编辑线索信息");
//				res =  updateTableData(newg_status, req) ;
//			}
//			
//			//新增公海线索同时加一条客户信息
//			saveCustomer(req);
//			
//			if(res.getRetCode()==0) {
//				commit_newg(newg_status);
//				res.setRetCode(0);
//				res.setRetMessage("OK");
//				res.setKey(clue_uuid) ;
//			}else{
//				rollback_newg(newg_status);
//				res.setRetCode(9);
//				//res.setRetMessage("error");
//			}
//		} catch (Exception e) {
//			e.printStackTrace();
//			rollback_newg(newg_status);
//		}
//		return res;
//	}
	
	//如果企业联系人手机号更换，则将原来联系人手机号所有的线索都换成新手机号
	public void updatePhone(Connection conn,String oldcluephone,String cluephone,String areaBelong,String companyId) throws SQLException{
		StringBuffer sb = new StringBuffer();
		sb.delete(0, sb.length());
		sb.append(" update clue set cluephone='").append(cluephone).append("' ,numberhome = '" + areaBelong + "' where cluephone='").append(oldcluephone).append("' and cluebelongtocompany='").append(companyId).append("' ");
		this.update(conn,sb.toString(),new Object[] {});
	}
	//新增线索时先将同公司同手机号的其他线索sfzx置为0
	public void updateSfzx(Connection conn,String cluephone,String companyId) throws SQLException{
		StringBuffer sb = new StringBuffer();
		sb.delete(0, sb.length());
		sb.append(" update clue set sfzx='0' where cluephone='").append(cluephone).append("' and cluebelongtocompany='").append(companyId).append("' ");
		this.update(conn,sb.toString(),new Object[] {});
	}
	//新增公海线索同时加一条客户信息
	public void saveCustomer(Connection conn,DataReq req) throws SQLException {
		Map<String, Object> param = req.getReqData();
		param.put("tableName", "customer");
		param.put("createUid",req.getUinfo().getId());
        param.put("isdelete","0");
        param.put("company_id", req.getUinfo().getCompanyId());
        param.put("phone", (String) param.get("cluephone"));
        param.put("customername", (String) param.get("companycontact"));
        param.put("longitude", (String) param.get("customerjd"));
        param.put("latitude", (String) param.get("customerwd"));
        param.put("address", (String) param.get("customeraddress"));
        
        //添加操作日志
  		param.put("OperationLogType", "02");
  		param.put("OperationLogId", "updateCustomerId");
  		//添加操作日志
  		
  			//拓展字段用
  			param.put("templateId", "");
  			param.put("jsondata", "[]");
  			//拓展字段用
  			
			//根据手机号查询是否有该客户
			StringBuffer sb = new StringBuffer();
			sb.delete(0, sb.length());
			sb.append(" select uuid from customer where phone='").append((String) param.get("oldcluephone")).append("' ");
			List<Map<String, Object>> list = this.queryForList(conn,sb.toString(),new Object[] {});
			String uuid="";
			if(list.size()>0){
				uuid=list.get(0).get("uuid").toString();
			}
			param.put("uuid", uuid);
			if(StringUtils.isEmpty(uuid)) {
				param.put("OperationLogName", "增加客户");
				param.put("OperationMemo", req.getUinfo().getNickName()+"添加了"+param.get("customername")+"客户");
				insertTableData(conn, req) ;
			}else {
				param.put("keys", "uuid");
				param.put("OperationLogName", "修改客户");
				param.put("OperationMemo", req.getUinfo().getNickName()+"修改了"+param.get("customername")+"客户信息");
				updateTableData(conn, req) ;
			}
		
	}
	
	public PageData getClueDetail(DataReq req) throws SQLException {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		Connection conn_crmeb = null;
		try {
			//获取线索基本信息
			sb.delete(0, sb.length()) ;
			sb.append(" select a.customeraddress,a.customerjd,a.customerwd,a.jsondata,a.templateId,a.uuid,a.cluestage,b.name cluestagename,a.followupstatus,c.name followupstatusname,a.poolentrymode,d.name poolentrymodename," +
					"a.cluesource,e.name cluesourcename,a.pooltype,if(f.name is null,a.pooltype,f.name) pooltypename,a.culestate,g.name culestatename,a.clueowner,h.nickName clueownername," +
					"a.cluename,a.cluephone,a.cluephone oldcluephone,a.numberhome,a.cluecompany,a.companyMemo,a.companycontact,a.personpost,o.`name` personpostName,a.businessdirection,a.companyfocus,a.projectname,a.xzqhdm,if(i.xzqhqc is null ,a.numberhome,i.xzqhqc) xzqhmc,a.projectaddress,a.projectarea,a.longitude,a.latitude," +
					"a.personincharge,a.personphone,if(a.sex='02','女','男') sex,a.wechat,a.memo,a.picture1,a.picture2,a.picture3,a.cluebelongtocompany,j.companyName companyName, " +
					"a.createUid,k.username createusername,date_format(a.createTime,'%Y-%m-%d %T') createTime,a.modifyUid,l.username modifyusername,date_format(a.modifyTime,'%Y-%m-%d %T') modifyTime," +
					"a.advertisementId,m.name,m.productId,n.productName,m.introduction,date_format(m.advertisingbegintime,'%Y-%m-%d %T') advertisingbegintime,date_format(m.advertisingendtime,'%Y-%m-%d %T')  advertisingendtime,m.advlink," +
					"a.newshopBookingId,p.name newshopBookingName,nbi.product_id,nbi.yyr_name,nbi.yyr_phone,date_format(nbi.createTime,'%Y-%m-%d %T') yysj,tc.mer_name,td.departmentName clueownerDepartmentName ") ;
			sb.append(" from clue a  ") ;
			sb.append(" left JOIN t_param b on a.cluestage=b.`value` and b.param_type='10'  ") ;
			sb.append(" left JOIN t_param c on a.followupstatus=c.`value` and c.param_type='11'  ") ;
			sb.append(" left JOIN t_param d on a.poolentrymode=d.`value` and d.param_type='12'  ") ;
			sb.append(" left JOIN t_param e on a.cluesource=e.`value` and e.param_type='13'  ") ;
			sb.append(" left JOIN t_param f on a.pooltype=f.`value` and f.param_type='14'  ") ;
			sb.append(" left JOIN t_param g on a.culestate=g.`value` and g.param_type='15'  ") ;
			sb.append(" left JOIN t_user h on a.clueowner=h.id  ") ;
			sb.append(" left JOIN t_department td on h.departmentId=td.departmentId ");
			sb.append(" left JOIN t_xzqh i on a.xzqhdm=i.xzqhdm  ") ;
			sb.append(" left JOIN t_company j on a.cluebelongtocompany=j.companyId  ") ;
			sb.append(" left JOIN t_user k on a.createUid=k.id  ") ;
			sb.append(" left JOIN t_user l on a.modifyUid=l.id  ") ;
			sb.append(" left JOIN advertisement m on a.advertisementId=m.uuid and m.isdelete='0' ") ;
			sb.append(" left join newshop_booking_info nbi on a.newshopBookingId =nbi.uuid and nbi.isdelete='0' ");
			sb.append(" left JOIN t_company tc on nbi.mer_id=tc.mer_id and tc.mer_id <> '' and tc.mer_id is not null ") ;
			sb.append(" left JOIN crm_product n on m.productId=n.uuid and n.isdelete='0'  ") ;
			sb.append(" left join t_param o on a.personpost=o.`value` and o.param_type='26' ");
			sb.append(" left join t_param p on a.newshopBookingId=p.`value` and p.param_type='200' ");
			
			sb.append("  where a.uuid= '").append((String)param.get("uuid")).append("' ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString());
			Map<String,Object> detail = (Map<String,Object>) list.get(0);

			detail.put("isFp", req.getUinfo().getCompanyId().equals(detail.get("cluebelongtocompany")) ? true : false);
			//订单信息
			if (ObjectUtil.isNotEmpty(detail.get("product_id"))) {
				sb.delete(0, sb.length());
				sb.append(" select store_name from eb_store_product where product_id=? ");
				List<Map<String, Object>> storeNameList = this.queryForList(conn_crmeb, sb.toString(), new Object[]{detail.get("product_id")});
				detail.put("store_name", storeNameList.size() == 0 ? "" : storeNameList.get(0).get("store_name"));
			}
			//线索列表
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT b.`name` cluesourceName,date_format(a.createTime, '%Y-%m-%d %T') createTime FROM clue a  ") ;
			sb.append(" LEFT JOIN t_param b ON a.cluesource = b.`value` AND b.param_type = '13' ") ;
			sb.append(" where a.cluephone=(select cluephone from clue where uuid=? ) and a.cluebelongtocompany=? ");
			List<Map<String, Object>> list0 = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{(String)param.get("uuid"),req.getUinfo().getCompanyId()});
			detail.put("sourcelist", list0);
			
			//获取线索图片
			for(int i=0;i<3;i++){
				if(!StringUtils.isEmpty((String) detail.get("picture"+(i+1)))){
					//压缩
					String urlStr1 = OssUtil.getImgSignURL((String) detail.get("picture"+(i+1)), 1);
					detail.put("imgReal"+(i+1),urlStr1);
					//不压缩
					String urlStr1_1 = OssUtil.getImgSignURL((String) detail.get("picture"+(i+1)), 0);
					detail.put("imgRealBig"+(i+1),urlStr1_1);
				}else{
					detail.put("imgReal"+(i+1),"");
					detail.put("imgRealBig"+(i+1),"");
				}
			}
			
			extData.put("clueInfo", list.get(0)) ;
			
			//跟进列表
			sb.delete(0, sb.length()) ;
			sb.append(" select a.uuid, a.clueId,b.cluename, a.customerId,c.customername, a.opportunityId,d.opportunityname,a.projectId,e.project_name," +
					" a.type,f.name typeName, a.userid, a.username, a.visittype,g.name visittypename,a.visitaddress,date_format(a.visittime,'%Y-%m-%d %T')  visittime,a.visitcontent, " +
					"a.createUid,k.username createusername,date_format(a.createTime,'%Y-%m-%d %T') createTime") ;
			sb.append(" from log_claim a  ") ;
			sb.append(" left JOIN clue b on a.clueId=b.uuid  ") ;
			sb.append(" left JOIN customer c on a.customerId=c.uuid  ") ;
			sb.append(" left JOIN opportunity d on a.opportunityId=d.uuid  ") ;
			sb.append(" left JOIN t_project e on a.projectId=e.uuid  ") ;
			sb.append(" left JOIN t_param f on a.type=f.`value` and f.param_type='18'  ") ;
			sb.append(" left JOIN t_param g on a.visittype=g.`value` and g.param_type='19'  ") ;
			sb.append(" left JOIN t_user k on a.createUid=k.id  ") ;

			sb.append("  where a.isdelete='0' and a.clueId= '").append((String)param.get("uuid")).append("'  ");
			list = this.getJdbcTemplate_newg().queryForList(sb.toString());
			extData.put("visitList", list) ;
			
			//商机列表
			sb.delete(0, sb.length()) ;
			sb.append(" select a.uuid, a.opportunityname,a.opportunitystage,b.name opportunitystagename,a.opportunitytype,c.name opportunitytypename," +
					"a.opportunityowner,d.username opportunityownername,a.projectaddress,a.projectarea,a.opportunitymoney,date_format(a.estimatedclosingdate,'%Y-%m-%d %T') estimatedclosingdate") ;
			sb.append(" from opportunity a  ") ;
			sb.append(" left JOIN t_param b on a.opportunitystage=b.`value` and b.param_type='16'  ") ;
			sb.append(" left JOIN t_param c on a.opportunitytype=c.`value` and c.param_type='17'  ") ;
			sb.append(" left JOIN t_user d on a.opportunityowner=d.id  ") ;

			sb.append("  where a.isdelete='0' and a.clueId= '").append((String)param.get("uuid")).append("' ");
			list = this.getJdbcTemplate_newg().queryForList(sb.toString());
			extData.put("opportunityList", list) ;
			
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {
			if (conn_crmeb != null)
				conn_crmeb.close();
		}
		return res;
	}
	
	public PageData addLogClaim(Connection conn,DataReq req) throws SQLException {
		PageData res = null;
		try {
			Map<String, Object> param = req.getReqData();
			res = new PageData();
			res.setRetCode(9);
			res.setRetMessage("拜访记录添加失败");
			StringBuffer sb = new StringBuffer();
			//添加拜访信息
			param.put("tableName", "log_claim");
			param.put("isdelete", "0");
			param.put("companyId", req.getUinfo().getCompanyId());
			param.put("keys", "uuid");
			param.put("uuid", (String)param.get("uuid"));
			param.put("OperationLogName", "增加线索跟进记录");
			param.put("OperationLogType", "01");
			insertTableData(conn, req) ;

			//修改线索跟进状态
			param.put("tableName", "clue");
			param.put("uuid", (String)param.get("clueId"));
			param.put("OperationLogName", "修改线索跟进状态");
			param.put("OperationLogType", "01");
			updateTableData(conn, req) ;

			//商城预约状态为 1已完成
			sb.delete(0,sb.length());
			sb.append("select newshopBookingId from clue where newshopBookingId is not null and newshopBookingId <> '' and uuid=? and isdelete=0 and sfzx=1 ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("clueId")});
			if (list !=null && list.size()>0){
				try {
					newg_status = begin_newg();// 开启事务
					sb.delete(0, sb.length());
					sb.append("update newshop_booking_info set booking_status='1',modifyUid=?,modifyTime=now() where uuid=?");
					this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{req.getUinfo().getId(),list.get(0).get("newshopBookingId")});
					commit_newg(newg_status);
				} catch (DataAccessException e) {
					e.printStackTrace();
					rollback_newg(newg_status);
				}
			}
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setKey((String)param.get("uuid")) ;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData deleteLogClaim(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();

		param.put("tableName", "log_claim");
		param.put("isdelete", "1");
		
		//添加操作日志

		param.put("OperationLogType", "01");
		param.put("OperationLogName", "删除线索跟进记录");

		newg_status = begin_newg();// 开启事物
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("error");
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			rollback_newg(newg_status);
		}
		return res;
	}
	
	
	public PageData addClueAdvertisement(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();

		param.put("tableName", "clue");
		
		//添加操作日志
		param.put("OperationLogType", "01");
		param.put("OperationLogName", "增加线索关联广告推广");
		param.put("OperationLogId", "updateClueId");
		newg_status = begin_newg();// 开启事物
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("error");
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
			
		} finally {
			rollback_newg(newg_status);
		}
		return res;
	}

	public PageData deleteClueAdvertisement(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();

		param.put("tableName", "clue");
		param.put("advertisementId", "");
		
		
		//添加操作日志
		param.put("OperationLogType", "01");
		param.put("OperationLogName", "删除线索关联广告推广");
		param.put("OperationMemo", (String)param.get("advertisementId"));
		param.put("OperationLogId", "updateClueId");
		newg_status = begin_newg();// 开启事物
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("error");
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
			
		} finally {
			rollback_newg(newg_status);
		}
		return res;
	}
	
	public String getClueUUIDListByUUID(String uuid){
		//先查该uuid对应的手机号和公司
		StringBuffer sb = new StringBuffer();
		sb.append(" select cluephone,cluebelongtocompany from clue where uuid='").append(uuid).append("' ");
		List<Map<String, Object>> listPhone = this.getJdbcTemplate_newg().queryForList(sb.toString());
		String cluephone=listPhone.get(0).get("cluephone").toString();
		String cluebelongtocompany=listPhone.get(0).get("cluebelongtocompany").toString();
		//在查该手机号公司下所有线索
		sb = new StringBuffer();
		sb.append(" select uuid from clue where cluephone='").append(cluephone).append("' and cluebelongtocompany='").append(cluebelongtocompany).append("' ");
		List<Map<String, Object>> listUuid = this.getJdbcTemplate_newg().queryForList(sb.toString());
		String uuidStr="";
		for(int i=0;i<listUuid.size();i++){
			uuidStr=uuidStr+"'"+listUuid.get(i).get("uuid")+"',";
		}
		uuidStr=uuidStr.substring(0, uuidStr.length()-1);
		return uuidStr;
	}

	public PageData changeClueOwer(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		
		StringBuffer sb = new StringBuffer();
		//生成商机后不能操作
		sb.delete(0, sb.length()) ;
		sb.append(" select count(*)") ;
		sb.append(" from opportunity a  ") ;
		sb.append(" where a.isdelete='0' and a.clueId= '").append((String)param.get("uuid")).append("'  ");
		int cnt = this.getJdbcTemplate_newg().queryForObject(sb.toString(), Integer.class);
		if (cnt>0){
			res.setRetCode(9);
			res.setRetMessage("已经生成商机，不能转移所有人");
			return res;
		}
		//转移线索，则转移当前线索
		String uuid=(String)param.get("uuid");
		//String uuidStr=getClueUUIDListByUUID(uuid);
		String isClick = (String) param.get("isClick");
		String newOwerUuid = (String) param.get("newOwerUuid");
		String oldOwerUuid = (String) param.get("oldOwerUuid");

		String culestate="";//线索状态：01已分配  02待分配
		String cluebelongtocompany ="0000";//线索所属公司
		String clueowner="";//线索所有人
		String OperationLogName="";
		String OperationMemo ="";
		List<Map<String, Object>> list =new ArrayList<>();
		if ("1".equals(isClick)){//公司
			culestate="02";
			cluebelongtocompany = newOwerUuid;
			//获取原所属公司信息
			sb.delete(0, sb.length()) ;
			sb.append("SELECT tc.companyName oldOwerName,(select companyName from t_company  where sfsc='0' and companyId=? ) newOwerName " +
					"FROM clue c\n" +
					"left join t_company tc on c.cluebelongtocompany=tc.companyId\n" +
					"where c.uuid=? ");
			list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{newOwerUuid,uuid});
			OperationLogName ="转移线索所属公司";
			OperationMemo="原线索公司："+list.get(0).get("oldOwerName")+",新线索公司："+list.get(0).get("newOwerName");
		}else if ("3".equals(isClick)){//个人
			culestate="01";
			clueowner = newOwerUuid;
			//获取所有人信息
			sb.delete(0, sb.length()) ;
			sb.append("select (select nickName from t_user where id=? ) oldOwerName,nickName newOwerName,companyId newCompanyId from t_user where id=? ");
			list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{oldOwerUuid,newOwerUuid});
			cluebelongtocompany = (String) list.get(0).get("newCompanyId");
			OperationLogName = "转移线索所有人";
			OperationMemo="原线索所有人："+list.get(0).get("oldOwerName")+"，新线索所有人："+list.get(0).get("newOwerName");
		}
		//添加操作日志
        //param.put("clueId", (String)param.get("uuid"));
		param.put("OperationLogType", "01");
		param.put("OperationLogName", OperationLogName);
		param.put("OperationMemo", OperationMemo);
		param.put("OperationLogId", "updateClueId");
		
		newg_status = begin_newg();// 开启事物
		try {
			param.put("culestate", culestate);
			sb.delete(0, sb.length());
			//sb.append(" update clue set culestate='01',clueowner='").append((String)param.get("newOwerUuid")).append("' where uuid in (").append(uuidStr).append(") ");
			sb.append(" update clue set culestate=?,cluebelongtocompany =? ,clueowner=? where uuid =? ");
			this.getJdbcTemplate_newg().update(sb.toString(),new Object[] {culestate,cluebelongtocompany,clueowner,uuid});
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setKey(uuid) ;
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			rollback_newg(newg_status);
		}
		return res;
	}
	
	public PageData claimClue(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();


		//添加操作日志
		param.put("OperationLogType", "01");
		param.put("OperationLogName", "认领线索");
		param.put("OperationMemo", "认领人："+(String)param.get("clueowner"));
		param.put("OperationLogId", "updateClueId");
		newg_status = begin_newg();// 开启事物
		try {
//			//认领线索，则认领同手机号同公司的所有线索
//			String uuid=(String)param.get("uuid");
//			String uuidStr=getClueUUIDListByUUID(uuid);
//			StringBuffer sb = new StringBuffer();
//			sb.append(" update clue set culestate='01',clueowner='").append((String)param.get("clueowner")).append("' where uuid in (").append(uuidStr).append(") ");
//			this.getJdbcTemplate_newg().update(sb.toString(),new Object[] {});
			
			//认领线索，只认领当前线索
			String uuid=(String)param.get("uuid");
			StringBuffer sb = new StringBuffer();
			sb.append(" update clue set culestate='01',clueowner='").append((String)param.get("clueowner")).append("' where uuid ='").append(uuid).append("' ");
			this.getJdbcTemplate_newg().update(sb.toString(),new Object[] {});
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setKey(uuid) ;
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		}finally {
			rollback_newg(newg_status);
		}
		return res;
	}
	
	public PageData returnClue(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();

		StringBuffer sb = new StringBuffer();
		//添加操作日志
		param.put("OperationLogType", "01");
		param.put("OperationLogName", "退回线索到线索池");
		param.put("OperationMemo", (String)param.get("returnReason"));
		param.put("OperationLogId", "updateClueId");
		newg_status = begin_newg();// 开启事物
		try {
			//生成商机后不能操作
			sb.delete(0, sb.length()) ;
			sb.append(" select count(*)") ;
			sb.append(" from opportunity a  ") ;
			sb.append(" where a.isdelete='0' and a.clueId= '").append((String)param.get("uuid")).append("'  ");
			int cnt = this.getJdbcTemplate_newg().queryForObject(sb.toString(), Integer.class);
			if (cnt>0){
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("已经生成商机，不能退回");
				return res;
			}
			
			//退回线索，当前线索
			sb.delete(0, sb.length());
			String departmentId = req.getUinfo().getDepartmentId();
			String companyId = req.getUinfo().getCompanyId();
			if (StringUtils.isEmpty(departmentId)) {
				List<Map<String, Object>> companyByCompanyId = companyDaoImpl.getCompanyByCompanyId(req);
				if (companyByCompanyId.size() > 0) {
					companyId= (String) companyByCompanyId.get(0).get("p_companyId");
				}
			}
			//退回  如果部门id为空，则退回上级公司 不为空，则退回本级公司
			String upcompanyId = StringUtils.isEmpty(companyId)?"0000":companyId;
			String uuid=(String)param.get("uuid");
			//String uuidStr=getClueUUIDListByUUID(uuid);
			sb.append(" update clue set poolentrymode='02',culestate='02',clueowner=null,cluebelongtocompany=?  where uuid ='").append(uuid).append("' ");
			this.getJdbcTemplate_newg().update(sb.toString(),new Object[] {upcompanyId});
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setKey(uuid) ;
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			rollback_newg(newg_status);
		}
		return res;
	}
	
	public PageData deleteClue(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		//判断clue是否已经生成了商机，如果生成了商机，不允许删除
		StringBuffer sb = new StringBuffer();
		//生成商机后不能操作
		sb.delete(0, sb.length()) ;
		sb.append(" select count(*)") ;
		sb.append(" from opportunity a  ") ;
		sb.append(" where a.isdelete='0' and a.clueId= '").append((String)param.get("uuid")).append("'  ");
		int cnt = this.getJdbcTemplate_newg().queryForObject(sb.toString(), Integer.class);
		if (cnt>0){
			res.setRetCode(9);
			res.setRetMessage("已经生成商机，不能删除");
			return res;
		}
		sb.delete(0, sb.length()) ;

		param.put("tableName", "clue");
		param.put("isdelete", "1");

		//添加操作日志
		//param.put("clueId", (String)param.get("uuid"));
		param.put("OperationLogType", "01");
		param.put("OperationLogName", "删除线索信息");
		param.put("OperationLogId", "updateClueId");


		newg_status = begin_newg();// 开启事物
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("error");
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
			
		} finally {
			rollback_newg(newg_status);
		}
		return res;
	}
	
	public PageData addAdvertisement(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();

		param.put("tableName", "advertisement");
		param.put("isdelete", "0");

		//添加操作日志
//		param.put("clueId", (String)param.get("uuid"));
		param.put("OperationLogType", "01");
		param.put("OperationLogName", "添加产品广告信息");
		param.put("belongtocompany", req.getUinfo().getCompanyId());
		newg_status = begin_newg();// 开启事物
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("error");
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			rollback_newg(newg_status);
		}
		return res;
	}
	
	public PageData deleteAdvertisement(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();

		param.put("tableName", "advertisement");
		param.put("isdelete", "1");

		//添加操作日志
//		param.put("clueId", (String)param.get("uuid"));
		param.put("OperationLogType", "01");
		param.put("OperationLogName", "删除产品广告信息");
		newg_status = begin_newg();// 开启事物
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("error");
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			rollback_newg(newg_status);
		}
		return res;
	}
	
	//广告列表查询
	public PageData getAdvertisement(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		try {
			
			//线索目列表
			sb.delete(0, sb.length()) ;
			sb.append("SELECT 	a.uuid,a.productId,b.productName,a.name,a.cluesource,c.name cluesourcename,a.introduction,date_format(a.advertisingbegintime,'%Y-%m-%d %T') advertisingbegintime,date_format(a.advertisingendtime,'%Y-%m-%d %T') advertisingendtime,a.advlink, " +
					"a.createUid,k.username createusername,date_format(a.createTime,'%Y-%m-%d %T') createTime,a.modifyUid,l.username modifyusername,date_format(a.modifyTime,'%Y-%m-%d %T') modifyTime") ;
			sb.append(" from advertisement a  ") ;
			sb.append(" left JOIN product b on a.productId=b.uuid and b.isdelete='0'  ") ;
			sb.append(" left JOIN t_param c on a.cluesource=c.`value` and c.param_type='13'  ") ;
			sb.append(" left JOIN t_user k on a.createUid=k.id  ") ;
			sb.append(" left JOIN t_user l on a.modifyUid=l.id  ") ;
			
		    sb.append("  where a.isdelete='0' and a.belongtocompany  like concat(?,'%')   ");
		   //广告名称
			if(!StringUtils.isEmpty((String)req.getReqData().get("name"))) {
				sb.append("  and a.name like   '%").append(req.getReqData().get("name")).append("%' ");
			}
			
			//流量来源
			if(!StringUtils.isEmpty((String)req.getReqData().get("cluesource"))) {
				sb.append("  and a.cluesource =  '").append(req.getReqData().get("cluesource")).append("' ");
			}
			
			
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] { req.getUinfo().getCompanyId() });
			sb.append(" order by  a.modifyTime desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			res.setPage(list);	
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {

		}
		return res;
	}

	//保存信息到线索池
    @SuppressWarnings("unchecked")
	public PageData saveToClue(DataReq req){
        PageData res = new PageData();
        final Map<String, Object> param = req.getReqData();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        res.setRetCode(9);
        res.setRetMessage("添加线索信息失败");
        newg_status = begin_newg();// 开启事物
        try {
            //保存客户信息
            param.put("tableName", "clue");
            param.put("cluestage","01");
            param.put("cluestate","02");
            param.put("followupstatus","01");
            param.put("poolentrymode","01");
            param.put("isdelete","0");
            param.put("createTime",sdf.format(new Date()));
            param.put("modifyTime",sdf.format(new Date()));
            PageData sqlInfo = getInsertSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            final StringBuffer sb_fieldMain = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listDataMain = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_fieldMain.toString(),listDataMain.toArray());
        } catch (Exception e) {
            rollback_newg(newg_status);
            e.printStackTrace();
        } finally {
			rollback_newg(newg_status);
        }
        commit_newg(newg_status);
        res.setKey((String)param.get("uuid")) ;
        res.setRetCode(0);
        res.setRetMessage("OK");

        //发送通知
        String token = getToken();
        //获取通知用户的openid
        List<Map<String, Object>> userOpenIds = userDaoImpl.getUserOpenId();
        String create_time = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date());
        String info = "您有一条新的线索加入公海中";
        for (Map<String,Object> userOpenId:userOpenIds){
            String openid = (String) userOpenId.get("openid");
            WxUtils.sendTempM(openid, token, create_time, info, "",1);
        }
        return res;
    }
    
    public String getToken() {
        StringBuffer sb = new StringBuffer();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection ctce_conn = null ;
        String token = null ;
        try {
            ctce_conn = this.getDBConnect_ctce()  ;/////getJdbcForCtce() ;

            sb.delete(0, sb.length());
            sb.append(" SELECT csz from t_xtcs where csm='accessToken' ");
            ps = ctce_conn
                    .prepareStatement(sb.toString(),
                            ResultSet.TYPE_SCROLL_SENSITIVE,
                            ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
            if (rs.next()) {
                token = rs.getString("csz") ;
            }
            rs.close();
            ps.close();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if(ctce_conn!=null) {
                    ctce_conn.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return token ;
    }
	
	public PageData getDepartmentTree(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取公司信息失败");
		try {

		/*	StringBuffer sb = new StringBuffer();
			sb.append(" select b.departmentId `value`,b.departmentName `label`,group_concat(a.nickName) username,group_concat(a.id) id " +
					" FROM t_user a " +
					" LEFT JOIN t_department b on a.departmentId=b.departmentId where 1=1 ");
			if (!StringUtils.isEmpty(req.getUinfo().getCompanyId())&&!"0000".equals(req.getUinfo().getCompanyId())){
				sb.append(" and b.p_companyId = '").append(req.getUinfo().getCompanyId()).append("' ") ;
			}
			sb.append(" GROUP BY b.departmentId ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			
			// 数据处理
			for (int j=0;j<list.size();j++) {
				String[] username=list.get(j).get("username").toString().split(",");
				String[] id=list.get(j).get("id").toString().split(",");
				List<Map<String, Object>> chlist=new ArrayList<Map<String, Object>>();
				for(int i=0;i<id.length;i++){
					Map<String, Object> map = new LinkedHashMap<String, Object>();
					map.put("value", id[i]);
					map.put("label", username[i]);
					chlist.add(map);
				}
				list.get(j).put("children", chlist);
			}*/

			String companyId = req.getUinfo().getCompanyId();
			StringBuffer sb = new StringBuffer();
			sb.append(" select * from ( ");
			sb.append(" SELECT '' id,'' username,a.companyId `value`,a.companyName `label`,a.p_companyId pId,a.p_companyName,'1' isClick from t_company a " +
					" where a.sfsc<>'1' ");
			sb.append(" UNION ");
			sb.append(" SELECT b.id,b.username,a.departmentId `value`,a.departmentName `label`,a.p_companyId pId,a.p_companyName,'2' isClick from t_department a " +
					" LEFT JOIN (SELECT GROUP_CONCAT(id) id,group_concat(nickName ) username,departmentId FROM t_user where sfsc<>'1' GROUP BY departmentId) b ON a.departmentId=b.departmentId " +
					" where a.sfsc<>'1' ");
			sb.append(" )x where 1=1 and `value` <> ? ");
			if (StringUtils.isNotEmpty(companyId) && !"0000".equals(companyId)) {
				sb.append(" and `value` like '").append(companyId).append("%' ");
			}
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId});
			// 数据处理
			List<Map<String, Object>> pList = new ArrayList<>();// eTree
			// 获取父节点
			for (Map<String, Object> tmp : list) {
				String cg_pig = "";
				if (companyId.equals(tmp.get("pId"))) {
				} else {
					cg_pig = tmp.get("pId").toString();
				}
				// 组装子节点，开始循环去获取子节点
				if (StringUtils.isEmpty(cg_pig)) {
					List<Map> value = companyChild(tmp.get("value").toString(), list);
					if (value.size() == 0 && ObjectUtil.isNotEmpty(tmp.get("id")) && "2".equals(tmp.get("isClick"))) {
						String[] username = tmp.get("username").toString().split(",");
						String[] userId = tmp.get("id").toString().split(",");
						for (int i = 0; i < userId.length; i++) {
							Map map1 = new LinkedHashMap<String, Object>();
							map1.put("value", userId[i]);
							map1.put("label", username[i]);
							map1.put("isClick", "3");
							value.add(map1);
						}
					}
					tmp.put("children", value);
					pList.add(tmp);
				}
			}
			res.setPage(pList);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {

		}
		return res;
	}

	public List<Map> companyChild(String id, List<Map<String, Object>> data) {
		List<Map> lists = new ArrayList<>();
		for (Map<String, Object> map : data) {
			String cg_pig = map.get("pId").toString();
			if (cg_pig.equals(id)) {
				// 组装子节点，开始循环去获取子节点
				List<Map> value = companyChild(map.get("value").toString(), data);
				if (value.size()==0 && ObjectUtil.isNotEmpty(map.get("id")) && "2".equals(map.get("isClick"))) {
					String[] username = map.get("username").toString().split(",");
					String[] userId = map.get("id").toString().split(",");
					for (int i = 0; i < userId.length; i++) {
						Map map1 = new LinkedHashMap<String, Object>();
						map1.put("value", userId[i]);
						map1.put("label", username[i]);
						map1.put("isClick", "3");
						value.add(map1);
					}
				}
				map.put("children", value);
				lists.add(map);
			}
		}
		return lists;
	}
	
	
	public PageData getActivityByID(Connection conn,DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取公司信息失败");
		try {
			String uuid = (String) req.getReqData().get("id") ;
			String isDefault = "0" ;
			if(StringUtils.isEmpty(uuid)||"null".equals(uuid)) {
				uuid = "00" ;
				isDefault = "1" ;
			}
			StringBuffer sb = new StringBuffer();
			sb.append(" select uuid,cluesource,`name`,subname,starttime,endtime,companyId,createUid from t_activity where uuid=? ");
			List<Map<String, Object>> list = this.queryForList(conn,sb.toString(), new Object[]{uuid});
			if(list.size()==1) {
				res.setData(list.get(0));
				res.setRetCode(0);
				res.setKey(isDefault);
				res.setRetMessage("OK");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public String getAreaCodeByPhone(DataReq req) {
		String res = "86" ;
		try {
			String phone = (String) req.getReqData().get("phone") ;
			Map<String, String> adderssMap = HttpUtils.getPhoneAddress(phone) ;
			req.getReqData().put("province", adderssMap.get("province")+" "+adderssMap.get("city")) ;
			
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT xzqhdm from t_xzqh where xzqhqc like concat(?,'%') and xzqhqc like concat('%',?,'%') order by xzqhjb limit 1 ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{adderssMap.get("province"),adderssMap.get("city")});
			if(list.size()>0) {
				res= (String)list.get(0).get("xzqhdm") ;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	public PageData getClueByID(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取公司信息失败");
		try {
			String userPhone = (String) req.getReqData().get("userPhone") ;
			String activeUuid = (String) req.getReqData().get("activeUuid") ;
			

			StringBuffer sb = new StringBuffer();
			sb.append(" select count(1) from clue where advertisementId=? and cluephone=? ");
			int cnt = this.getJdbcTemplate_newg().queryForObject(sb.toString(), new Object[]{activeUuid,userPhone}, Integer.class);
			if(cnt==0) {
				res.setRetCode(0);
				res.setRetMessage("OK");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getClueByPhone(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询错误！");
		try {
			String userPhone = (String) req.getReqData().get("userPhone") ;
			String newshopBookingId = (String) req.getReqData().get("newshopBookingId");
			String exists = (String) req.getReqData().get("exists");
			StringBuffer sb = new StringBuffer();
			sb.append(" select uuid,cluename,cluephone from clue where newshopBookingId=? and cluephone=? and isdelete=0");
			if ("1".equals(exists)) {
				// 存在12小时内创建的
				sb.append(" and createTime > DATE_SUB(NOW(),INTERVAL 12 HOUR)");
				sb.append(" and followupstatus <> '06' ");
			}
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{newshopBookingId,userPhone});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}


	public PageData getAdList(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取广告列表失败");
		Connection ctce_conn = null ;
		try {
			ctce_conn = this.getDBConnect_newg();
			String adType = (String) req.getReqData().get("adType");
			StringBuffer sb = new StringBuffer();
			Object[] objects;
			if (StrUtil.isBlank(adType)) {
				sb.delete(0, sb.length());
				sb.append(" select * from ad");
				objects = new Object[]{};
			} else {
				sb.delete(0, sb.length());
				sb.append(" select * from ad where adType=?");
				objects = new Object[]{adType};
			}

			List<Map<String, Object>> list = this.queryForList(ctce_conn, sb.toString(), objects);
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("ok");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getClueCheckCnt(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		try {
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT * FROM( ");
			sb.append(" SELECT a.uuid,a.companycontact,a.cluephone,a.numberhome,a.cluecompany,a.pooltype,a.personpost,a.businessdirection,count(1) cnt FROM clue a where sfzx='1' and a.isdelete='0' and a.cluebelongtocompany like concat(?,'%') ");
			sb.append(" GROUP BY cluephone ");
			sb.append(" )aa WHERE aa.cnt>1 ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] {req.getUinfo().getCompanyId() });
			if(list.size()>0){
				for(int i=0;i<list.size();i++){
					List<Map<String, Object>> sublist =getClueCheckDetail(req.getUinfo().getCompanyId(),(String)list.get(i).get("cluephone"));
					list.get(i).put("sublist",sublist);
				}
			}
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public List<Map<String, Object>> getClueCheckDetail(String companyId,String cluephone) {
		StringBuffer sb = new StringBuffer();
		List<Map<String, Object>> list =new ArrayList<Map<String, Object>>();
		try {
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT a.uuid,a.companycontact,a.cluephone,a.numberhome,a.cluecompany,a.pooltype,a.personpost,a.businessdirection FROM clue a where sfzx='1' and a.isdelete='0' and a.cluebelongtocompany like concat(?,'%') and a.cluephone=? ");
			 list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] {companyId,cluephone});
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return list;
	}

	public PageData delCheckClue(Connection conn,DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		try {
			JSONArray uuidArr= (JSONArray) param.get("uuidArr");
			for(int i=0;i<uuidArr.size();i++){
				param.put("tableName", "clue");
				param.put("isdelete", "1");
				param.put("keys", "uuid");
				param.put("uuid", uuidArr.get(i));
				res =  updateTableData(conn, req) ;
				if(res.getRetCode()!=0) {
					return res ;
				}
			}

			if(res.getRetCode()==0) {
				res.setRetCode(0);
				res.setRetMessage("OK");
			}

		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();

		} finally {

		}
		return res;
	}

	public PageData claimClueMore(Connection conn,DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		try {
			JSONArray uuidArr= (JSONArray) param.get("uuidArr");
			for(int i=0;i<uuidArr.size();i++){
				param.put("tableName", "clue");
				param.put("culestate", "01");
				param.put("clueowner",param.get("clueowner"));
				param.put("keys", "uuid");
				param.put("uuid", uuidArr.get(i));
				res =  updateTableData(conn, req) ;
				if(res.getRetCode()!=0) {
					return res ;
				}
			}

			if(res.getRetCode()==0) {
				res.setRetCode(0);
				res.setRetMessage("OK");
			}

		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();

		} finally {

		}
		return res;
	}

	public PageData changeClueOwerMore(Connection conn,DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		try {
			JSONArray uuidArr= (JSONArray) param.get("uuidArr");
			for(int i=0;i<uuidArr.size();i++){
				//生成商机后不能操作
				sb.delete(0, sb.length()) ;
				sb.append(" select count(*)") ;
				sb.append(" from opportunity a  ") ;
				sb.append(" where a.isdelete='0' and a.clueId= '").append(uuidArr.get(i)).append("'  ");
				int cnt = this.getJdbcTemplate_newg().queryForObject(sb.toString(), Integer.class);
				if (cnt>0){
					res.setRetCode(9);
					res.setRetMessage("已经生成商机，不能转移或分配所有人");
					return res;
				}

				//转移线索，则转移当前线索
				String uuid=(String)uuidArr.get(i);
				String isClick = (String) param.get("isClick");
				String newOwerUuid = (String) param.get("newOwerUuid");

				String culestate="";//线索状态：01已分配  02待分配
				String cluebelongtocompany ="0000";//线索所属公司
				String clueowner="";//线索所有人
				String OperationLogName="";
				String OperationMemo ="";
				List<Map<String, Object>> list =new ArrayList<>();
				if ("1".equals(isClick)){//公司
					culestate="02";
					cluebelongtocompany = newOwerUuid;
					//获取原所属公司信息
					sb.delete(0, sb.length()) ;
					sb.append("SELECT tc.companyName oldOwerName,(select companyName from t_company  where sfsc='0' and companyId=? ) newOwerName " +
							"FROM clue c\n" +
							"left join t_company tc on c.cluebelongtocompany=tc.companyId\n" +
							"where c.uuid=? ");
					list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{newOwerUuid,uuid});
					OperationLogName ="转移线索所属公司";
					OperationMemo="原线索公司："+list.get(0).get("oldOwerName")+",新线索公司："+list.get(0).get("newOwerName");
				}else if ("3".equals(isClick)){//个人
					culestate="01";
					clueowner = newOwerUuid;
					//获取所有人信息
					sb.delete(0, sb.length()) ;
					sb.append("select (select nickName from t_user where id=(SELECT clueowner from clue where uuid=? )) oldOwerName,nickName newOwerName,companyId newCompanyId from t_user where id=? ");
					list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{uuid,newOwerUuid});
					cluebelongtocompany = (String) list.get(0).get("newCompanyId");
					OperationLogName = "转移线索所有人";
					OperationMemo="原线索所有人："+list.get(0).get("oldOwerName")+"，新线索所有人："+list.get(0).get("newOwerName");
				}
				//添加操作日志
				param.put("OperationLogType", "01");
				param.put("OperationLogName", OperationLogName);
				param.put("OperationMemo", OperationMemo);
				param.put("OperationLogId", "updateClueId");
				//修改线索信息
				param.put("tableName", "clue");
				param.put("culestate", culestate);
				param.put("cluebelongtocompany", cluebelongtocompany);
				param.put("clueowner",clueowner);
				param.put("keys", "uuid");
				param.put("uuid", uuid);
				res =  updateTableData(conn, req) ;
				if(res.getRetCode()!=0) {
					return res ;
				}
			}

			if(res.getRetCode()==0) {
				res.setRetCode(0);
				res.setRetMessage("OK");
			}

		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();

		} finally {

		}
		return res;
	}

	public PageData getCheckin(Connection conn, DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sbfrom = new StringBuffer();
		StringBuffer select = new StringBuffer();
		try {
			String unionid = ParameterUtil.getString(req, "unionid");
			select
					.append(" select a.newshopBookingId,p.name newshopBookingName,a.companycontact as userName,")
					.append(" a.cluephone       as userPhone,")
					.append(" a.companyMemo     as `desc`,")
					.append(" a.sex             as userSex,")
					.append(" a.openid          as openId,")
					.append(" a.customeraddress as userXxdz,")
					.append(" a.cluesource,")
					.append(" a.unionid, a.uuid as id, a.followupstatus, a.createTime as time");
			sbfrom
					.append(" from clue a")
					.append(" left join t_param p on a.newshopBookingId=p.`value` and p.param_type='200' ")
					.append(" where a.newshopBookingId like '90__'")
					.append(" and a.unionid = ?");
			Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{unionid});
			select.append(sbfrom)
					.append(" order by a.createTime desc")
					.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.queryForList(conn, select.toString(), new Object[]{unionid});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {
		}
		return res;
	}

	public PageData cancelCheckin(Connection conn, DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		try {
			String id = ParameterUtil.getString(req, "id");
			sb.append("update clue set followupstatus = '06' where uuid = ? ");
			final int update = this.update(conn, sb.toString(), new Object[]{id});
			if (update > 0) {
				res.setRetCode(0);
				res.setRetMessage("OK");
			} else {
				res.setRetCode(9);
				res.setRetMessage("操作失败！");
			}
		} catch (Exception e) {
			res.setRetCode(9);
			res.setRetMessage("操作失败！");
			e.printStackTrace();
		} finally {
		}
		return res;
	}
}